﻿using AccessApiService.Model;
using System;
using System.Collections.Generic;
using System.Data.OleDb;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;
using System.Data;
using System.Diagnostics;
using System.Data.SqlClient;
using ZK.Common.Log4;
using ZK.Common;

namespace AccessApiService
{

    //sql server

    // 注意: 使用“重构”菜单上的“重命名”命令，可以同时更改代码和配置文件中的类名“AccessApiService”。
    public class AccessApiService : IAccessApiService
    {
        SqlConnection sqldb = new SqlConnection(AppConfigHelper.GetAppSettings("SConStr"));
        OleDbConnection oledb = new OleDbConnection(AppConfigHelper.GetAppSettings("AConStr"));//SConStr
        string dataBase = AppConfigHelper.GetAppSettings("DataBase");
        public ApiLoadingResponseDto GetTruckInfo(TruckInfoDto truckInfo)
        {
            LogManage.WriteInfo(Newtonsoft.Json.JsonConvert.SerializeObject(truckInfo));
            if (dataBase == "S")
            {
                try
                {
                    //@"Data Source = LS--20180714CSF\SQL2018; Initial Catalog = db_19; Integrated Security = True";
                    //server=ip,1433;database=db;uid=sa;pwd=123456;
                    EventLog log = new EventLog();
                    log.Source = "zkkjbd";
                    ApiLoadingResponseDto reDto = new ApiLoadingResponseDto();
                    //try
                    //{
                    LogManage.WriteInfo("打开数据库");
                    sqldb.Open();

                    LogManage.WriteInfo("已经打开数据库");

                    //string s1 = "SELECT [车号],[毛重],[皮重],[净重],[货物名称],[发货单位],[收货单位],[运输单位],[毛重时间],[皮重时间],[作废标志],[毛重计量员],[皮重计量员],[称重日期] FROM Detail ";
                    string s1 = "select ch as [车号] ," +
                        "mz as [毛重] ," +
                         "pz as [皮重] ," +
                          "jz as [净重] ," +
                           "wzmc as [货物名称] ," +
                           "gf as [发货单位] ," +
                           "sf as [收货单位] ," +
                           "wsdw as [运输单位] ," +
                           "mzsj as [毛重时间] ," +
                           "pzsj as [皮重时间] ," +
                           "scbz as [作废标志] ," +
                           "f.yhxm as [毛重计量员] ," +
                           "f.yhxm as [皮重计量员] ," +
                           "sj as [称重日期] " +
                     // " from czjlb a  ";
                     " from czjlb a  LEFT OUTER JOIN dbo.yhb f ON a.mzjly = f.yhbm  LEFT OUTER JOIN dbo.yhb f1 ON a.pzjly = f1.yhbm ";

                    string s2 = "";
                    if (truckInfo.车牌号 != "")
                    {
                        s2 = "where a.ch = '" + truckInfo.车牌号 + "' and  sj >=convert(datetime,'" + truckInfo.起始时间 + "',102) and sj <=convert(datetime,'" + truckInfo.结束时间 + "',102) ";
                    }
                    else if (truckInfo.车牌号 == "")
                    {
                        s2 = "WHERE  sj >=convert(datetime,'" + truckInfo.起始时间 + "',102) and sj <=convert(datetime,'" + truckInfo.结束时间 + "',102)";
                    }

                    string sql = s1 + s2 + " ORDER BY sj DESC";
                    LogManage.WriteInfo(sql);
                    SqlDataAdapter dbAdapter = new SqlDataAdapter(sql, sqldb);
                    //LoadingRecord lr = new LoadingRecord();
                    DataTable dt = new DataTable("dt1");
                    //dt.TableName = "TaskList";
                    dbAdapter.Fill(dt);
                    if (dt != null)
                    {
                        reDto.Success = true;
                        reDto.Msg = "查询磅单记录成功";
                        reDto.dt = dt;
                    }
                    else if (dt == null)
                    {
                        reDto.Success = true;
                        reDto.Msg = "磅单记录为空";
                    }
                    else
                    {
                        reDto.Success = false;
                        reDto.Msg = "查询磅单记录失败";
                    }
                    sqldb.Close();
                    //}
                    //catch (Exception ex)
                    //{
                    // log.WriteEntry(ex.Message + "\n" + ex.StackTrace);
                    // }
                    return reDto;
                }
                catch (Exception ex)
                {
                    LogManage.WriteInfo(ex.ToString());
                    return null;
                }
            }
            if (dataBase == "A")
            {
                LogManage.WriteInfo("this is A");
                EventLog log = new EventLog();
                log.Source = "zkkjbd";
                ApiLoadingResponseDto reDto = new ApiLoadingResponseDto();
                try
                {
                    oledb.Open();
                    string s1 = "SELECT [车号],[毛重],[皮重],[净重],[货物名称],[发货单位],[收货单位],[运输单位],[毛重时间],[皮重时间],[作废标志],[毛重计量员],[皮重计量员],[称重日期] FROM Detail ";
                    string s2 = "";
                    //truckInfo.车牌号 != "" && truckInfo.起始时间 != null && truckInfo.结束时间 != null
                    if (truckInfo.车牌号 != "")
                    {
                        s2 = "WHERE 车号 = '" + truckInfo.车牌号 + "'AND 毛重时间 BETWEEN #" + truckInfo.起始时间 + "# AND #" + truckInfo.结束时间 + "#";
                    }
                    //truckInfo.车牌号 == "" && truckInfo.起始时间 != null && truckInfo.结束时间 != null
                    else if (truckInfo.车牌号 == "")
                    {
                        s2 = "WHERE 毛重时间 BETWEEN #" + truckInfo.起始时间 + "# AND #" + truckInfo.结束时间 + "#";
                    }
                    //else if (truckInfo.车牌号 == "" && truckInfo.起始时间 != null && truckInfo.结束时间 == null) {
                    //    s2 = "WHERE 毛重时间 BETWEEN #" + truckInfo.起始时间 + "# AND #" + DateTime.Now.ToString() + "#";
                    //}
                    //else if (truckInfo.车牌号 != "" && truckInfo.起始时间 == null && truckInfo.结束时间 != null) {
                    //    s2 = "WHERE 车号 = '" + truckInfo.车牌号 + "'";
                    //}
                    //else if (truckInfo.车牌号 != "" && truckInfo.起始时间 != null && truckInfo.结束时间 == null) {
                    //    s2 = "WHERE 车号 = '" + truckInfo.车牌号 + "'AND 毛重时间 BETWEEN #" + truckInfo.起始时间 + "# AND #" + DateTime.Now.ToString() + "#";
                    //}
                    //else if (truckInfo.车牌号 != "" && truckInfo.起始时间 == null && truckInfo.结束时间 == null) {
                    //    s2 = "WHERE 车号 = '" + truckInfo.车牌号 + "'";
                    //}
                    string sql = s1 + s2 + " ORDER BY 毛重时间 DESC";
                    LogManage.WriteInfo(sql);
                    OleDbDataAdapter dbAdapter = new OleDbDataAdapter(sql, oledb);
                    //LoadingRecord lr = new LoadingRecord();
                    DataTable dt = new DataTable("dt1");
                    //dt.TableName = "TaskList";
                    dbAdapter.Fill(dt);
                    if (dt != null)
                    {
                        reDto.Success = true;
                        reDto.Msg = "查询磅单记录成功";
                        reDto.dt = dt;
                    }
                    else if (dt == null)
                    {
                        reDto.Success = true;
                        reDto.Msg = "磅单记录为空";
                    }
                    else
                    {
                        reDto.Success = false;
                        reDto.Msg = "查询磅单记录失败";
                    }
                    oledb.Close();
                }
                catch (Exception ex)
                {
                    log.WriteEntry(ex.Message + "\n" + ex.StackTrace);
                    LogManage.WriteInfo(ex.ToString());
                }
                return reDto;
            }
            return null;
        }
        public ApiLoadingResponseDto GetSummaryInfo(TruckSummaryInfoDto truckSummaryInfo)
        {
            LogManage.WriteInfo(Newtonsoft.Json.JsonConvert.SerializeObject(truckSummaryInfo));
            if (dataBase == "S")
            {
                LogManage.WriteInfo(Newtonsoft.Json.JsonConvert.SerializeObject(truckSummaryInfo));
                EventLog log = new EventLog();
                log.Source = "zkkjsum";
                ApiLoadingResponseDto reDto = new ApiLoadingResponseDto();
                try
                {
                    sqldb.Open();
                    string s1 = " ";
                    //truckSummaryInfo.煤种 != null && truckSummaryInfo.收货单位 != null && truckSummaryInfo.起始时间 != null && truckSummaryInfo.结束时间 != null
                    if (truckSummaryInfo.煤种 != "" && truckSummaryInfo.收货单位 != "")
                    {
                        s1 = " WHERE a.wzmc = '" + truckSummaryInfo.煤种 + "'AND sj >=convert(datetime,'" + truckSummaryInfo.起始时间 + "',102) and sj <=convert(datetime,'" + truckSummaryInfo.结束时间 + "',102) ";

                    }
                    else if (truckSummaryInfo.煤种 == "" && truckSummaryInfo.收货单位 != "")
                    {
                        s1 = " WHERE a.sf = '" + truckSummaryInfo.收货单位 + "'AND sj >=convert(datetime,'" + truckSummaryInfo.起始时间 + "',102) and sj <=convert(datetime,'" + truckSummaryInfo.结束时间 + "',102) ";
                    }
                    else if (truckSummaryInfo.煤种 != "" && truckSummaryInfo.收货单位 == "")
                    {
                        s1 = " WHERE a.wzmc = '" + truckSummaryInfo.煤种 + "'AND sj >=convert(datetime,'" + truckSummaryInfo.起始时间 + "',102) and sj <=convert(datetime,'" + truckSummaryInfo.结束时间 + "',102) ";
                    }
                    else if (truckSummaryInfo.煤种 == "" && truckSummaryInfo.收货单位 == "")
                    {
                        s1 = " WHERE sj >=convert(datetime,'" + truckSummaryInfo.起始时间 + "',102) and sj <=convert(datetime,'" + truckSummaryInfo.结束时间 + "',102)  ";
                    }
                    string sql = "SELECT sum(jz) AS 煤总量,count(ch)AS 车数,sf as 收货单位, wzmc as 货物名称 " +
                        "from czjlb a  LEFT OUTER JOIN dbo.yhb f ON a.mzjly = f.yhbm  LEFT OUTER JOIN dbo.yhb f1 ON a.pzjly = f1.yhbm" +
                        " "
                        + s1 + "GROUP BY sf, wzmc";
                    LogManage.WriteInfo(sql);
                    SqlDataAdapter dbAdapter = new SqlDataAdapter(sql, sqldb);
                    DataTable dt = new DataTable("dt1");
                    dbAdapter.Fill(dt);
                    if (dt != null)
                    {
                        reDto.Success = true;
                        reDto.Msg = "查询磅单汇总成功";
                        reDto.dt = dt;
                    }
                    else if (dt == null)
                    {
                        reDto.Success = true;
                        reDto.Msg = "磅单汇总记录为空";
                    }
                    else
                    {
                        reDto.Success = false;
                        reDto.Msg = "查询磅单汇总失败";
                    }
                    sqldb.Close();
                }
                catch (Exception ex)
                {
                    LogManage.WriteInfo(ex.ToString());
                    log.WriteEntry(ex.Message + "\n" + ex.StackTrace);
                    sqldb.Close();
                }
                return reDto;
            }
            if (dataBase == "A")
            {
                LogManage.WriteInfo("this is A");
                EventLog log = new EventLog();
                log.Source = "zkkjsum";
                ApiLoadingResponseDto reDto = new ApiLoadingResponseDto();
                try
                {
                    oledb.Open();
                    string s1 = " ";
                    //truckSummaryInfo.煤种 != null && truckSummaryInfo.收货单位 != null && truckSummaryInfo.起始时间 != null && truckSummaryInfo.结束时间 != null
                    if (truckSummaryInfo.煤种 != "" && truckSummaryInfo.收货单位 != "")
                    {
                        s1 = " WHERE 货物名称 = '" + truckSummaryInfo.煤种 + "'AND 收货单位 = '" + truckSummaryInfo.收货单位 + "'AND 毛重时间 BETWEEN #" + truckSummaryInfo.起始时间 + "# AND #" + truckSummaryInfo.结束时间 + "# ";

                    }
                    else if (truckSummaryInfo.煤种 == "" && truckSummaryInfo.收货单位 != "")
                    {
                        s1 = " WHERE 收货单位 = '" + truckSummaryInfo.收货单位 + "'AND 毛重时间 BETWEEN #" + truckSummaryInfo.起始时间 + "# AND #" + truckSummaryInfo.结束时间 + "# ";
                    }
                    else if (truckSummaryInfo.煤种 != "" && truckSummaryInfo.收货单位 == "")
                    {
                        s1 = " WHERE 货物名称 = '" + truckSummaryInfo.煤种 + "'AND 毛重时间 BETWEEN #" + truckSummaryInfo.起始时间 + "# AND #" + truckSummaryInfo.结束时间 + "# ";
                    }
                    else if (truckSummaryInfo.煤种 == "" && truckSummaryInfo.收货单位 == "")
                    {
                        s1 = " WHERE 毛重时间 BETWEEN #" + truckSummaryInfo.起始时间 + "# AND #" + truckSummaryInfo.结束时间 + "# ";
                    }
                    string sql = "SELECT sum(净重) AS 煤总量,count(车号)AS 车数,收货单位, 货物名称 FROM Detail" + s1 + "GROUP BY 收货单位, 货物名称";
                    LogManage.WriteInfo(sql);
                    OleDbDataAdapter dbAdapter = new OleDbDataAdapter(sql, oledb);
                    DataTable dt = new DataTable("dt1");
                    dbAdapter.Fill(dt);
                    if (dt != null)
                    {
                        reDto.Success = true;
                        reDto.Msg = "查询磅单汇总成功";
                        reDto.dt = dt;
                    }
                    else if (dt == null)
                    {
                        reDto.Success = true;
                        reDto.Msg = "磅单汇总记录为空";
                    }
                    else
                    {
                        reDto.Success = false;
                        reDto.Msg = "查询磅单汇总失败";
                    }
                    oledb.Close();
                }
                catch (Exception ex)
                {
                    log.WriteEntry(ex.Message + "\n" + ex.StackTrace);
                    LogManage.WriteInfo(ex.Message);
                }
                return reDto;
            }
            return null;
        }
    }

}
